﻿using CRL.Data;
using QueryTest.Code;

namespace QueryTest.test
{
    public class join
    {
        public static void testView()
        {
            //关联再关联子查询
            var q1 = Code.OrderRepository.Instance.GetLambdaQuery();
            var q2 = q1.CreateQuery<Code.ProductData>();
            q2.Where(b => b.Id > 0);
            var view = q2.CreateQuery<Code.Member>().Where(b => b.Id > 0).GroupBy(b => b.Name).Select(b => new { b.Name, aa = b.Id.COUNT() });//GROUP查询
            var view2 = q2.Join(view, (a, b) => a.CategoryName == b.Name).Select((a, b) => new { ss1 = a.UserId, ss2 = b.aa });//关联GROUP
            var join = q1.Join(view2, (a, b) => a.Id == b.ss1).Select((a, b) => new { a.Id, b.ss1 }).OrderBy(b=>b.ss1);
            //q1.Join(view2, (a, b) => a.Id == b.ss1).SelectAppendValue(b => b.ss1);//存入索引
            //var result = view2.ToList();
            var sql = q1.PrintQuery();
            var result = join.ToList();
        }
        public static void testJoin()
        {
            var query = ProductDataRepository.Instance.GetLambdaQuery();
            query.Where(b => b.Id == 1 || b.IsTop == true);
            var join = query.Join<Member>((a, b) => a.Id == b.Id);
            join.Where((a, b) => b.Name.Like("1") || b.Id == 2);
            //.Join<Order>((a, b, c) => a.UserId == c.UserId).JoinAfter(b=>b.Status==1).Select((a, b, c) => new { b.Id });
            var result = join.Select((a, b) => new { a.BarCode, b.AccountNo }).ToList();
            query.PrintQuery();
        }
        public static void testJoin3()
        {
            //关联再GROUP
            var query = ProductDataRepository.Instance.GetLambdaQuery();
            var join = query.Join<Code.Member>((a, b) => a.UserId == b.Id);
            join.GroupBy((a, b) => new { a.ProductName, b.Name });

            var sql = query.PrintQuery();
            var result = join.Select((a, b) => new { a.ProductName, b.Name }).ToList();
        }

        public static void testSelfJoin()
        {
            var query = Code.OrderRepository.Instance.GetLambdaQuery();
            var query2 = query.CreateQuery<Order>();
            query2.Where(b => b.OrderId == "222");
            var view = query2.Select(b => new { b.OrderId });
            query.Join(view, (a, b) => a.OrderId == b.OrderId);
            var sql = query.PrintQuery();
            query.ToList();

        }
        public static void testJoin5()
        {
            //子查询关联子查询
            var query = ProductDataRepository.Instance.GetLambdaQuery();
            var view1 = query.Where(b => b.SoldPrice < 10).GroupBy(b => new { b.CategoryName, b.ProductName }).Select(b => new { cname = b.CategoryName, b.ProductName });
            var q2 = query.CreateQuery<Code.ProductData>();

            var view2 = q2.Where(b => b.Id > 1).GroupBy(b => new { b.CategoryName, b.ProductName }).Select(b => new { cname2 = b.CategoryName, b.ProductName });
            var join = view1.Join(view2, (a, b) => a.cname == b.cname2);
            var view3 = join.Select((a, b) => new { a.cname });
            view3.PrintQuery();

            var q3 = query.CreateQuery<Member>();
            var view4 = q3.Join(view3, (a, b) => a.Name == b.cname).Select((a, b) => new { a.Name, b.cname });
            view4.PrintQuery();

            var q5 = view3.AsQuery();
            var view5 = q5.GroupBy(b => b.cname).Select(b => new { b.cname });
            view5.PrintQuery();

            var result = view5.ToList();
        }
    }
}
